// 倒入相关的包
const xlsx = require('node-xlsx');
const fs = require('fs');

// 解析对应的xlsx文件
const sheets = xlsx.parse('Performance Report .xlsx');

// 定义缓存每个sheet里的内容变量
const cach = {};

// 定义缓存根据Device Sub ID拆分子sheet的变量
const deviceSubIdCach = {};

// 定义清洗后的数据
const freshData = [];

// 缓存读取所有的表
for (const sheet of sheets) {
  // 检测原表格哪些数据有值
  // if (sheet.data.length > 0) {
  //   console.log(sheet['name']) // 1 2 3 20 Performance Report
  // }
  cach[sheet['name']] = sheet['data'];
}

// console.log(cach['Performance Report'][1]); // [ 'Device Sub ID', 'Performance Parameter', 'Parameter Value', 'Time' ]
// 读取sheet Performance Report里的内容
for (const rowId in cach['Performance Report']) {
  // console.log(cach['Performance Report'][rowId]);
  if (Number(rowId) > 1) {
    const [index, ...rest] = cach['Performance Report'][rowId];
    if (index !== '0') {
      if (deviceSubIdCach[index]) {
        deviceSubIdCach[index].push(rest);
      } else {
        deviceSubIdCach[index] = [rest];
      }
    }
  }
}
// console.log(deviceSubIdCach['1']);

// 清洗表里面的内容
let round12 = [];
for (const key in deviceSubIdCach) {
  deviceSubIdCach[key].sort((a, b) => {
    return new Date(a.slice(-1)) - new Date(b.slice(-1));
  });
  freshData.push(
    JSON.parse(
      JSON.stringify({
        name: key,
        data: [
          [
            'ID',
            'CPU (average',
            'CPU (max)',
            'Radio Module 7 DL Output Power',
            'Radio Module 6 DL Output Power',
            'Radio Module 5 DL Output Power',
            'Radio Module 4 DL Output Power',
            'Radio Module 3 DL Output Power',
            'Radio Module 2 DL Output Power',
            'Radio Module 1 DL Output Power',
            'Radio Module 8 DL Output Power',
            'TIME',
          ],
        ],
      })
    )
  );
  for (const data of deviceSubIdCach[key]) {
    const [parameter, value, time] = data;
    if (round12.length === 10) {
      round12.push(value, time);
      if (round12[0] === key) {
        freshData[key - 1]['data'].push(JSON.parse(JSON.stringify(round12)));
      }
      round12.length = 0;
    } else if (round12.length === 0) {
      round12 = [key, value];
    } else {
      round12.push(value);
    }
  }
}

// console.log(deviceSubIdCach['1'])
console.log(freshData[16]);

// 生成清洗后的文件
const buffer = xlsx.build(freshData);
fs.writeFile('result.xlsx', buffer, (err) => {
  if (err) {
    console.log('Write faill: ', err);
    return;
  }
  console.log('Write completed');
});
